Metadata is basically a logical DB schema which gets stored locally in Helical Insight server (as a .metadata extension) and allows users to define which tables, columns they would want to keep. Thus, different Metadata can be created for the different set of users like finance metadata, sales metadata, operations metadata which then can be shared with the relevant set of people. This will not only increase the performance but also help users to see only relevant tables and columns they are interested in, thus aiding in ease of usage.
Metadata further also allows users to define/edit “Joins” (over and above present in the database which can also be edited at metadata level), give “Alias” name for the tables and column and apply “Data security”.
To create a metadata, a user has to create a data source initially. Without a data source, metadata cannot be created. There are two methods of creating metadata.
Method 1: Once the data source is created then on the “view” tab you can see the listings of the data source for that specific database. You can click on “Create Metadata” icon and you will get navigated to create-metadata page.
Method 2: You can click on the “Metadata” button on the top ribbon. Then it will navigate to created metadata page.
Note:With version 4.0 onwards, on the left side, we only show the list of databases for which atleast one connection has been made. Earlier all the DBicons were visible even if there were no connections also made for those Databases.
Step 2: After you have finished first step (using either method 1 or method 2) you will be on the metadata page and on the left side you can see listings of the data sources created by you or shared with you. If the datasource has been shared with you as “Execute Only” permission then it will not appear here in this list. Navigate to the required data source and click on it.
Step 3: You can see the list of catalogs and schemas present. Select the required schema.
Step 4: Now you can click on the catalog and see the list of tables present in that catalog. This list is already cached. You can abort the operation by clicking on the line that appears below the catalog/schema name while loading the table list.
Search:There is a search option available which also supports wild character (regex) search which can also be used for quicker navigation.
More Functions: Clicking on the three dots allows you certain additional functionalities like to refresh the connection (especially useful if meanwhile more tables and columns have been added), edit the data source.
Note:
- If the data source connection credentials have been changed at the data source page and the application is not able to make the connection to the data source it will appear as red.
- Clicking on Refresh will delete the existing cache entry and prepare a new cache for the connection. Due to this, some time may be taken for loading the tables.
Step 5: By default, none of the tables and associated columns are selected. Select the tables you are interested to keep in the metadata. It is always advisable to keep only the limited tables in the metadata which are required for reporting purposes. You can also create multiple metadata’s for different set of roles/functional requirements.
Once the selections are made you can either drag into the second selection interface or right click and use the option “Add to Metadata”. The selected tables and columns will appear like the below.
– With the version 4.1.1 GA , we have added a new feature “pagination”. On to the left side where all the data sources will be present , when you click on a datasources , the schema will be loaded which contains all the tables.
– From the version 4.1.1 GA onwards, the tables will be loaded page wise as you can see in the below image , there is a pagination provide which is highlighted (yellow portion). Clicking on that icon will load the next set of tables. This functionality is helpful when there are a lot of tables even then the browser page will not hang.
– When you click on that the next page of the tables will be loaded (ex :50 tables in every page) and in the below you can also see the selected tables
Step 6: Once the tables are added to the metadata, on clicking on a particular table name, the columns within that table will be loaded. This operation can be aborted by clicking on the line that appears below the table name while loading.
You can right-click on the table/column names and perform certain operations.
The first operation is “Alias”. You can rename the tables and columns in order to give it a simpler name (This is especially crucial since the metadata might be used by non-tech users for reports creation). You can rename any table/column this way.
The second operation is “Duplicate”. You can make a duplicate of a table or column by this operation. Name of the duplicate table/column is the same as the original table/column with an additional _1 added to the file name. This can be renamed as explained above. Duplicate of a column appears in the same table with the nomenclature as described.
Note: Before you make a duplicate of already duplicated table/column it is important to save the metadata.
The third operation is “Remove”. This will allow you to remove any table/column from the metadata.
Naming the metadata file: You can double click on the metadata file name and give it another different file name.
Search: It is also having a search operation supported allowing wild character (regex) search as well of the tables and columns names.
NOTE : From version 4.1.1 GA , when we add tables to the metadata , pagination is provided here also. Here you can view 50 tables per page and you can go to the further pages and also to the back pages with the pagination option given . This helps in faster performance
Step 7: Info: Info tab allows you to change the metadata data source connection detail. For example, you have created metadata and reports from the development database, now you want the metadata to point to the production database. In that case, you can click on “Change Datasource” and change the metadata to now point to your production database.
Step 8: Joins: Based on the kind of Primary Keys and Foreign keys present at the database it will show you list of joins. In case if there is any joining condition which is present on any column which is not part of the metadata it will appear as highlighted red (as shown below). Those can be deleted. You can also use “Delete Invalid Joins” functionality present near the “Add” button.
You can delete the already existing joins by pressing on delete button. You could also change the join type as well.
Also you can add more joins by clicking on “Add” Button, for specifying the columns you can either drag the columns from the metadata selection panel into the selection box or merely type the name of a column and then select from the drop-down. The sequence of joins can also be changed by dragging them (present next to the selection checkbox).
NOTE : – From version 4.1.1 GA , pagination is provided at the joins to
– Here at the very bottom you can see the pagination provided, when you make joins more than 10, then from the 11th join it will appear in a new page. Every page shows 10 joins by default.
– You can also select the number of joins that you want to see in a single page. At the top right you can see ( Showing page 1/43,10 per page) .Click on that box and you can select number of joins that you wish to view in a page.
Step 9: Views: In certain cases certain reports might require a lot of complex calculations. Hence in those cases “Views” can be used. Views allow you to write your own SQL queries and the output of that SQLQuery will be visible as a listing (view) along with tables and columns in the metadata. This view can also be used by an end user to create reports.In order to create the view, click on the “Add” button.
From version 4.0 onwards, there are 2 options to create a view – Query and Dynamic Query. If you are migrating from older versions, those queries will be part of “Query” section.
- Query View
- Dynamic View
User can create a view or sub-query without or with user session variables by double clicking on it available session variables.
You can create a simple query view with no session variables as shown below:
Example : select * from "meeting_details" where "meeting_id" in(5,7,11)
After entering the query, click on “Retrieve Columns” button to verify the query.
Once the column names are populated, click on “Save View” button to save the view. When the view is saved, it will be added to the metadata pane.
From version 4.0 onwards, you can also use the GroovySessionVariables in the query section of the view. You can use attributes related to the ${user}, ${organisation}, ${role} and ${profile}. Below is a sample example of a query using GroovySessionVariables.
Example : select * from "meeting_details" where "meeting_id" = ${user}.id
For more detailed information on the various session variables and attributes that can be used, please refer to this blog.
Sometimes, you may want to modify the query on runtime based on the input parameters selected by end user as well as the session variables/loggedin users credentials (like his name, role, organization, profiles etc). Different filter values may trigger different queries in such cases.
To achieve this easily, dynamic query view feature has been added at metadata level in Helical Insight.
Dynamic query essentially implies that the query is generated at runtime depending on the user inputs. This can be achieved by adding a dynamic logic/groovy script using which end user can use the filters, form data expressions and groovy user session variables on runtime.
Adhoc report can be created using metadata with dynamic view where in report inputs will be taken from either metadata or from report or from both.
A report with dynamic view gives the better query performance in report along with runtime sql query execution.
The various sections of the dynamic query view are shown in the image below:
You can refer this blog to learn about dynamic query views and the expression usage in detail
After writing the query, click on “Retrieve Columns” to verify that the SQL is correct.
After that, click on “Validate” to validate the syntax of the groovy script that has been written. Once the validation of both SQL and groovy script is successful, click on “Save View” button. The view will be added to the metadata
Step 10: Security: You can use the same metadata to create report/dashboard which will be shared with multiple users. In that case, we may want to make sure that even though same report/dashboard is shared with everyone they get to see their own data only. In those cases, metadata security conditions need to be implemented.
Table level, row level, and column level security conditions can be implemented over an organization, roles, user or profile.
To learn more about metadata security implementation click here
To learn metadata security conditions usage click here
Step 11: Saving the metadata: Once the metadata is created with all the joins, security conditions, views, naming etc then you can click on “Save”, the file browser will appear. You can save the metadata in a folder of your choice. By right clicking in file browser you can also create your own folder inside which you can save the metadata.
Please note that if your metadata is having any invalid joins or blank joins then it will throw appropriate message.
Note: Save Metadata creates a copy of the tables and columns in the cache. Hence, saving metadata may take some time.
Step 12: Share: You can click on the share button to share the metadata. If you want to share a report with a user it is also essential to share the metadata from which the report is created.
In order to share the metadata it is important that the metadata is saved first.
Once you click on the share you will have the option of sharing the metadata with an individual user, a role or an organization also. Please note that you can only share with a role/user who is part of your organization and not outside the organization. Whereas a superadmin can share it across anyone.
While sharing you can also specify the permission level.
You are also having the option of sharing from the file browser also. Please refer to
Sharing Metadata with Organization
Note: If the number of tables and columns in your database is huge, then the metadata page automatically becomes really huge and heavy it may cause the browser memory to be full. This in turn will slow down the page and might even cause crashing of the browser page. In order to overcome this, you could implement the following:
1. At the database level, create a user with access to only limited number of tables depending on the reporting requirements, then use this specific user to connect to the database at helical insight datasources layer. Automatically, the performance at metadata will improve due to limited number of tables.
2. Second option is on the datasources page, you can limit how much information gets displayed at the metadata layer (a sort of prefiltering). This can be done by going to “Advanced” option in the datasource connection.
In the “Other Options” placeholder, you can add restricting strings such as below:
?HI_SCHEMA_CONTAINS=hi (this will show only the schema names that contain the string hi)
?HI_CATALOG_CONTAINS=t (this will show only the catalog names that contain t)
?HI_CATALOG=travel_data (this will show only travel_data catalog)
Similar strings and regular expression (regex) can be used to restrict the database tables, schema, cataglos that are visible at the metadata level.
So with above method you can initially restrict the size of listing on the metadata page so that it is fast and make initial metadata. Then you can edit the already created datasource to include other table/schema (using the above mentioned method), then edit the metadata created earlier (refresh the cache) and then select additional tables required and make it part of the metadata by using the merge option. This way your create-metadata browser page will never be so heavy and will work fine.
Edit Metadata
To edit the metadata we have two methods:
Method 1: Directly from the file browser we have to navigate to the folder wherever we have saved the metadata, by right-clicking, we will get an option called “Edit Metadata” click on that and edit the metadata.
Method 2: From the top ribbon you can directly click on the “Metadata” tab that will redirect us to the metadata page. Once on the metadata-create page click on “Edit” which will open the file browser and from there we have to navigate to the folder where we have saved the metadata either double-click on the metadata or we can right click and edit the metadata.
Changing the Data Source
Step 1: In order to change the data source that the metadata is pointing to, click on Change Data Source in the Info section. You will be asked to select the data source.
Step 2:Select the type of connection.
Step 3:Select the data source from the connection list.
You can also type the schema and catalog names by entering the relevant names in the boxes provided.
Adding Tables to Metadata
Select the tables that you want to add to metadata. Then right click and select Add to Metadata.
Select whether you want to Merge or Reload.
Merge will append the new tables to the list of existing tables in the Metadata.
Reload will delete the original tables and load the tables that have been selected.
After adding new tables, on clicking on Joins, there is again a prompt to select Merge or Reload.
Merge will add the new joins to the list of existing joins in the Metadata.
Reload will delete the original joins and load the new joins according to the tables that are part of the Metadata.
For more information you can email on support@helicalinsight.com